package es.ats.codington.festival.daos;

import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.imageio.ImageIO;


import es.ats.codington.festival.domain.Imagen;
import es.ats.codington.festival.domain.Place;

import es.ats.codington.festival.exceptions.DAOException;
import es.ats.codington.festival.interfaces.daos.IPlaceDAO;
import es.ats.codington.festival.utils.FERSDataConnection;
import es.ats.codington.festival.utils.FERSDbQuery;

public class PlaceDAOImpl implements IPlaceDAO{

	
	Connection conn;
	int key;
	
	/**
	 * Default constructor for PlaceDAOImpl
	 * @param conn
	 */
	public PlaceDAOImpl(Connection conn){
		this.conn = conn;
	}
	
	
	/**
	 * obtain a list with every place in the database table
	 * @param resultSet
	 * @return places
	 * @throws SQLException
	 */
	private List<Place> getPlaceList(ResultSet resultSet) throws SQLException {
		
		List<Place> places = new ArrayList<Place>();
		
		while(resultSet.next()) {
			
			Place place = new Place();
			
		    place.setId(resultSet.getInt("idPlace"));
		    place.setNamePlace(resultSet.getString("namePlace"));
		    place.setCapacityPlace(resultSet.getInt("capacityPlace"));
		    place.setPlaceDescription(resultSet.getString("placeDescription"));
		    place.setZonePlace(resultSet.getString("zonePlace"));
		    place.setImagePlace(resultSet.getString("imagePlace"));
		 
		    places.add(place);
		    
		}
		
		return places;
	}
	/**
	 * add a new place to the database table
	 * @param typePlace
	 * @param place
	 * @return insertPlace
	 * @throws DAOException 
	 */
	@Override
	public boolean insertPlace(Place place, String typePlace) throws DAOException {
		
		boolean insertPlace = false;
		int typePlaceNum = 0;
		Integer affectedRows = 0;	
		PreparedStatement preparedStatement = null;
		String sql = FERSDbQuery.getInsertPlace();
					 			
		try {
		    preparedStatement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		    
		    preparedStatement.setString(1, place.getNamePlace());
		    preparedStatement.setInt(2, place.getCapacityPlace());
		    preparedStatement.setString(3, place.getPlaceDescription());
		    preparedStatement.setString(4, place.getZonePlace());
		    preparedStatement.setString(5, place.getImagePlace());
		    
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		    
		    ResultSet keys = preparedStatement.getGeneratedKeys();
			keys.next();
			key = keys.getInt(1);
			
			place.setId(key);
			
			
			if(typePlace.equals("LBussines")){
				
				typePlaceNum = 1;
				
			} else if(typePlace.equals("Museum")){
				
				typePlaceNum = 2;
				
			} else if(typePlace.equals("Park")){
				
				typePlaceNum = 3;
				
			} else if(typePlace.equals("Stadium")){
				
				typePlaceNum = 4;
				
			} else if(typePlace.equals("Theater")){
				
				typePlaceNum = 5;
				
			} else if(typePlace.equals("TAttaction")){
				
				typePlaceNum = 6;
				
			} else if(typePlace.equals("TMarket")){
				
				typePlaceNum = 7;
				
			} else if(typePlace.equals("Zoo")){
				
				typePlaceNum = 8;
				
			} else {
				
				typePlaceNum = 1;
			}
			
			switch(typePlaceNum){
				//LBusiness-Building
				case 1:
					sql = "INSERT INTO building (idBuilding) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					sql = "INSERT INTO lbussines (idLBussines) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
					
				//Museum-Building
				case 2:
					sql = "INSERT INTO building (idBuilding) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					sql = "INSERT INTO museum (idMuseum) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
				//Park
				case 3:
					sql = "INSERT INTO park (idPark) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
				//Stadium-Building
				case 4:
					sql = "INSERT INTO building (idBuilding) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					
					sql = "INSERT INTO stadium (idStadium) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					//FERSDataConnection.commit(conn);
					
					break;
					
				//Theater-Building
				case 5:
					sql = "INSERT INTO building (idBuilding) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					sql = "INSERT INTO theater (idTheater) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
				//TAttraction
				case 6:
					sql = "INSERT INTO tattraction (idTAttraction) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
				//TMarket
				case 7:
					sql = "INSERT INTO tmarket (idTMarket) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
					
					break;
					
				//Zoo
				case 8:
					sql = "INSERT INTO zoo (idZoo) values (?)";
					preparedStatement = conn.prepareStatement(sql);
					preparedStatement.setInt(1, key);
					preparedStatement.executeUpdate();
			
					break;
					
			}
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}
		if(affectedRows > 0){
			insertPlace = true;
		}
			
		return insertPlace;
	}
	
	
	/**
	 * Allows us update all the information relative to a place
	 * @param place
	 * @return affectedRows
	 * @throws DAOException
	 */
	@Override
	public int updatePlace(Place place) throws DAOException {
		
		Integer affectedRows = 0;
		PreparedStatement preparedStatement = null;	
		String sql = FERSDbQuery.getUpdatePlace();
				 				
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    
		    preparedStatement.setString(1, place.getNamePlace());
		    preparedStatement.setInt(2, place.getCapacityPlace());
		    preparedStatement.setString(3, place.getPlaceDescription());
		    preparedStatement.setString(4, place.getZonePlace());
		    preparedStatement.setString(5, place.getImagePlace());
		    preparedStatement.setInt(6, place.getId());
		    
		    
		    affectedRows = preparedStatement.executeUpdate();
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {	   
		    FERSDataConnection.closeStatement(preparedStatement);
		    
		}
			
		return affectedRows;
		
	}
	
	/**
	 *show a list of all places
	 *@return places 
	 *@throws DAOException 
	 */
	@Override
	public List<Place> showAllPlaces() throws DAOException {
		
		List<Place> places = new ArrayList<Place>();	
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		String sql = FERSDbQuery.getAllPlaces();	
		
		try {
		    preparedStatement = conn.prepareStatement(sql);
		    
		    resultSet =  preparedStatement.executeQuery();
		    
		    places = this.getPlaceList(resultSet);
		    
		} catch(SQLException ex) {
		    throw new DAOException(ex.getMessage(), ex);
		    
		} finally {
		    FERSDataConnection.closeStatement(preparedStatement);
		    FERSDataConnection.closeResultSet(resultSet);
		    
		}	
			
		return places;	
	}
	
	
	/**
	 * TRATAMIENTO DE IM�GENES
	 */
	
	/**
	 * @param ruta
	 * @param nombre
	 */
	
	public boolean guardarImagen(String ruta,String nombre){
		
		//conn = FERSDataConnection.getConnection();
        String insert = "INSERT INTO images(image,name) values(?,?)";
        FileInputStream fis = null;
        PreparedStatement ps = null;
        try {
            
        	conn.setAutoCommit(false);
            File file = new File(ruta);
            fis = new FileInputStream(file);
            ps = conn.prepareStatement(insert);
            ps.setBinaryStream(1,fis,(int)file.length());
            ps.setString(2, nombre);
            ps.executeUpdate();
            conn.commit();
            
            return true;
        } catch (Exception ex) {
         
        }finally{
            try {
                ps.close();
                fis.close();
                //conn.close();
            } catch (Exception ex) {
            
            }
        }        
        return false;
    }
	
	/**
	 * 
	 * @return lista
	 */
    public List<Imagen> getImagenes() {
    	//conn = FERSDataConnection.getConnection();
    	PreparedStatement st = null;
    	ResultSet rs = null;
    	
        List<Imagen> lista = new ArrayList<Imagen>();
        try {
        	st = conn.prepareStatement("SELECT image,name FROM images;");
            rs = st.executeQuery(); 
            while (rs.next())
            {
                Imagen imagen=new Imagen();
                Blob blob = rs.getBlob("image");
                String nombre = rs.getObject("name").toString();
                byte[] data = blob.getBytes(1, (int)blob.length());
                BufferedImage img = null;
                try {
                    img = ImageIO.read(new ByteArrayInputStream(data));
                } catch (IOException ex) {
                 
                }
                
                imagen.setImage(img);
                imagen.setName(nombre);
                lista.add(imagen);
            }
            rs.close();
            st.close();
            conn.close();
        } catch (SQLException ex) {
            
        }
        return lista;
    }    

	
	//public static void main(String[] args) {
		
		/*Connection conn;
		List<Imagen> images = new ArrayList<Imagen>();
		conn = FERSDataConnection.getConnection();
		PlaceDAOImpl pdi = new PlaceDAOImpl(conn);
		String ruta = "C:\\Users\\JAVA101_01\\Desktop\\Database.png";
		String nombre = "img_database1";
		pdi.guardarImagen(ruta, nombre);
		images = pdi.getImagenes();
		System.out.println();*/
		
		/*Connection conn;
		PlaceDAOImpl placeDAO;
		Place place1;
		
		
		conn = FERSDataConnection.getConnection();
		placeDAO = new PlaceDAOImpl(conn);
	
		
		place1 = new Place();
		
		place1.setNamePlace("Stadium Camp Nou");
		place1.setCapacityPlace(90000);
		place1.setPlaceDescription("Footbal Stadium");
		place1.setZonePlace("SOUTH");
		place1.setImagePlace("");
		
		placeDAO.insertPlace(place1, "Stadium");
		
		
		try{
			FERSDataConnection.commit(conn);
			FERSDataConnection.closeConnection(conn);
		}catch(DAOException ex){}

	}*/



}
